import logging
from datetime import datetime, timedelta
from typing import Any, Dict, Optional

from sqlalchemy import func, or_, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.system import SysLog, SysUser
from app.schemas.base import Pagination
from app.schemas.log import LogPageVO

logger = logging.getLogger(__name__)


class LogService:
    def __init__(self, session: AsyncSession):
        self.session = session

    async def get_log_page(
        self, page_num: int, page_size: int, keywords: Optional[str], start_date: datetime, end_date: datetime
    ) -> Pagination[LogPageVO]:
        """
        获取日志分页列表
        :param page_num: 页码
        :param page_size: 每页记录数
        :param keywords: 关键字
        :param start_date: 开始日期
        :param end_date: 结束日期
        :return: 日志分页列表
        """
        # 构建查询语句
        stmt = (
            select(SysLog, SysUser.nickname)
            .join(SysUser, SysLog.create_by == SysUser.id, isouter=True)
            .where(SysLog.is_deleted == 0)
        )
        if keywords:
            stmt = stmt.where(
                or_(
                    SysLog.content.ilike(func.concat("%", keywords, "%")),
                    SysLog.ip.ilike(func.concat("%", keywords, "%")),
                    SysUser.nickname.like(func.concat("%", keywords, "%")),
                )
            )
        if start_date and end_date:
            stmt = stmt.where(func.date(SysLog.create_time).between(start_date, end_date))
        if start_date and not end_date:
            stmt = stmt.where(func.date(SysLog.create_time) >= start_date)

        # total 总数
        # 先获取符合条件的总记录数，用于分页信息展示
        total_query = select(func.count()).select_from(stmt.subquery())
        result = await self.session.execute(total_query)
        total = result.scalar()

        # 计算分页偏移量：当前页码减1乘以每页大小
        offset = (page_num - 1) * page_size
        stmt = stmt.order_by(SysLog.create_time.desc()).offset(offset).limit(page_size)
        # 执行查询
        result = await self.session.execute(stmt)
        rs = result.fetchall()
        items = [
            LogPageVO(
                id=item.id,
                module=item.module,
                content=item.content,
                request_uri=item.request_uri,
                method=item.method,
                ip=item.ip,
                region="localhost" if item.ip == "127.0.0.1" else f"{item.province}-{item.city}",
                browser=item.browser,
                os=item.os,
                execution_time=item.execution_time,
                create_by=item.create_by,
                create_time=item.create_time,
                operator=nickname,
            )
            for item, nickname in rs
        ]
        return Pagination[LogPageVO](total=total, list=items, page=page_num, size=page_size)

    async def get_pv_counts(self, start_time: datetime, end_time: datetime) -> Dict[str, Any]:
        """
        获取指定时间段内的PV（Page Views）数量
        :param start_time: 开始时间
        :param end_time: 结束时间
        :return: PV数量
        """
        # 构建查询语句
        stmt = select(func.date(SysLog.create_time).label("date"), func.count(1).label("count")).where(
            func.date(SysLog.create_time).between(start_time, end_time), SysLog.is_deleted == 0
        )
        stmt = stmt.group_by(func.date(SysLog.create_time))
        stmt = stmt.order_by(func.date(SysLog.create_time).desc())
        # 执行查询
        result = await self.session.execute(stmt)
        # 处理结果
        pv_counts = {row.date.strftime("%Y-%m-%d"): row.count for row in result}
        return pv_counts

    async def get_ip_counts(self, start_time: datetime, end_time: datetime) -> Dict[str, Any]:
        """
        获取指定时间段内的IP数量
        :param start_time: 开始时间
        :param end_time: 结束时间
        :return: IP数量
        """
        # 构建查询语句
        stmt = select(
            func.date(SysLog.create_time).label("date"), func.count(func.distinct(SysLog.ip)).label("count")
        ).where(func.date(SysLog.create_time).between(start_time, end_time), SysLog.is_deleted == 0)
        stmt = stmt.group_by(func.date(SysLog.create_time))
        result = await self.session.execute(stmt)
        # 处理结果
        ip_counts = {row.date.strftime("%Y-%m-%d"): row.count for row in result}
        return ip_counts

    async def get_pv_stats(
        self, start_time: Optional[datetime] = None, end_time: Optional[datetime] = None
    ) -> Dict[str, Any]:
        """
        获取PV（Page Views）统计信息

        Args:
            start_time: 开始时间（可选，默认为今天）
            end_time: 结束时间（可选，默认为当前时间）

        Returns:
            Dict[str, Any]: 包含今日访问量、总访问量和增长率的字典
        """
        # 设置默认时间范围
        if start_time is None:
            start_time = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
        if end_time is None:
            end_time = datetime.now()

        # 计算昨天同一时间段
        yesterday_start = start_time - timedelta(days=1)
        yesterday_end = end_time - timedelta(days=1)

        try:
            # 方案1：分别查询各个统计值，在Python层面计算
            # 今日访问量
            today_count_stmt = select(func.count(1)).where(
                SysLog.create_time.between(start_time, end_time), SysLog.is_deleted == 0
            )
            today_result = await self.session.execute(today_count_stmt)
            today_count = today_result.scalar() or 0

            # 昨天同一时间段访问量
            yesterday_count_stmt = select(func.count(1)).where(
                SysLog.create_time.between(yesterday_start, yesterday_end), SysLog.is_deleted == 0
            )
            yesterday_result = await self.session.execute(yesterday_count_stmt)
            yesterday_count = yesterday_result.scalar() or 0

            # 总访问量
            total_count_stmt = select(func.count(1)).where(SysLog.is_deleted == 0)
            total_result = await self.session.execute(total_count_stmt)
            total_count = total_result.scalar() or 0

            # 计算增长率（在Python层面处理，避免PostgreSQL兼容性问题）
            if yesterday_count == 0:
                growth_rate = 0.0
            else:
                growth_rate = round((today_count - yesterday_count) / yesterday_count, 2)

            return {
                "todayCount": today_count,
                "totalCount": total_count,
                "growthRate": growth_rate,
            }

        except Exception as e:
            # 异常处理：返回默认值
            logger.error(f"获取PV统计信息失败: {e}")
            return {"todayCount": 0, "totalCount": 0, "growthRate": 0.0}

    async def get_uv_stats(
        self, start_time: Optional[datetime] = None, end_time: Optional[datetime] = None
    ) -> Dict[str, Any]:
        """
        获取UV（Unique Visitors）统计信息

        Args:
            start_time: 开始时间（可选，默认为今天）
            end_time: 结束时间（可选，默认为当前时间）

        Returns:
            Dict[str, Any]: 包含今日UV、总UV和增长率的字典
        """
        # 设置默认时间范围
        if start_time is None:
            start_time = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
        if end_time is None:
            end_time = datetime.now()

        # 计算昨天同一时间段
        yesterday_start = start_time - timedelta(days=1)
        yesterday_end = end_time - timedelta(days=1)

        try:
            # 方案1：分别查询各个统计值，在Python层面计算
            # 今日UV
            today_uv_stmt = select(func.count(func.distinct(SysLog.ip))).where(
                SysLog.create_time.between(start_time, end_time), SysLog.is_deleted == 0
            )
            today_result = await self.session.execute(today_uv_stmt)
            today_uv = today_result.scalar() or 0

            # 昨天同一时间段UV
            yesterday_uv_stmt = select(func.count(func.distinct(SysLog.ip))).where(
                SysLog.create_time.between(yesterday_start, yesterday_end), SysLog.is_deleted == 0
            )
            yesterday_result = await self.session.execute(yesterday_uv_stmt)
            yesterday_uv = yesterday_result.scalar() or 0

            # 总UV
            total_uv_stmt = select(func.count(func.distinct(SysLog.ip))).where(SysLog.is_deleted == 0)
            total_result = await self.session.execute(total_uv_stmt)
            total_uv = total_result.scalar() or 0

            # 计算增长率（在Python层面处理，避免PostgreSQL兼容性问题）
            if yesterday_uv == 0:
                growth_rate = 0.0
            else:
                growth_rate = round((today_uv - yesterday_uv) / yesterday_uv, 2)

            return {
                "todayCount": today_uv,
                "totalCount": total_uv,
                "growthRate": growth_rate,
            }

        except Exception as e:
            # 异常处理：返回默认值
            logger.error(f"获取UV统计信息失败: {e}")
            return {"todayCount": 0, "totalCount": 0, "growthRate": 0.0}
